/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Controller;

import Model.Usuario;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author Iron
 */
public class UsuarioDAO {
    
    public static final UsuarioDAO INSTANCE = new UsuarioDAO();
    private BancoDeDados bd;
    
    
    public UsuarioDAO(){
        bd = new BancoDeDados();
 
    }
    
    
    public void insert(String login, String senha, String nome) throws Exception{
        bd.conectar();
        bd.sql = "INSERT INTO \"Usuario\" values (?,?,?)";
        bd.comandoSql = bd.connection.prepareStatement(bd.sql);
        bd.comandoSql.setString(1, login);
        bd.comandoSql.setString(2, senha);
        bd.comandoSql.setString(3, nome);
        bd.comandoSql.executeUpdate();
        bd.desconectar();
    }
    
    public void delete(String login) throws SQLException, Exception{
        bd.conectar();
        bd.sql = "DELETE FROM \"Usuario\" where login = ?";
        bd.comandoSql = bd.connection.prepareStatement(bd.sql);
        bd.comandoSql.setString(1, login);
        bd.comandoSql.executeUpdate();
        bd.desconectar();
    }
    
    public ArrayList<Usuario> lista() throws SQLException, Exception{
        bd.conectar();
        bd.sql = "SELECT * FROM \"Usuario\"";
        bd.comandoSql = bd.connection.prepareStatement(bd.sql);
        ResultSet rs = bd.comandoSql.executeQuery();
        ArrayList<Usuario> lista = new ArrayList<Usuario>();
        String nome;
        String login;
        String senha;
        while(rs.next()){
          login = rs.getString(1);
          senha = rs.getString(2);
          nome = rs.getString(3);
          lista.add(new Usuario(login,senha,nome));
        }       
        bd.desconectar();
        return lista;
    }
    
    public Usuario search(String login) throws Exception{
        bd.conectar();
        bd.sql = "SELECT * FROM \"Usuario\" where login = ?";
        bd.comandoSql = bd.connection.prepareStatement(bd.sql);
        bd.comandoSql.setString(1, login);
        ResultSet rs = bd.comandoSql.executeQuery();
        
        String senha  = null ;
        String nome   = null  ;

        
        while(rs.next()){         
            senha  = rs.getString(2);
            nome   = rs.getString(3);

        }
        bd.desconectar();
    
        return new Usuario(login, senha, nome);
    }
    
    
    public void alterarSenha(String login, String senha) throws SQLException, Exception{
        bd.conectar();
        bd.sql = "UPDATE \"Usuario\" set senha = ? where login = ?";
        bd.comandoSql = bd.connection.prepareStatement(bd.sql);
        bd.comandoSql.setString(1,senha);
        bd.comandoSql.setString(2,login);
        bd.comandoSql.executeUpdate();
        bd.desconectar();
    }
    
}
